package org.ekstrabilet.stadium.logic;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.ekstrabilet.database.connection.ConnectionManager;
import org.ekstrabilet.database.statements.Statements;
import org.ekstrabilet.stadium.beans.Sector;
import org.ekstrabilet.stadium.beans.Tribune;
/**
 * Singleton, provides logic for Tribune object, responsible for
 * manipulating that object in database
 * @author Marcin
 *
 */
public class TribuneLogic {

	private static TribuneLogic tribuneLogic= null;
	
	private TribuneLogic(){

	}
	/**
	 * creates new entity in database based on Tribune object
	 * @param tribune Tribune object
	 * @return id of added tribune
	 * @throws SQLException
	 */
	public int addTribune(Tribune tribune) throws SQLException{
		Connection conn= ConnectionManager.getManager().getConnection();
		try {
			PreparedStatement stm= conn.prepareStatement(Statements.INSERT_TRIBUNE);
			stm.setInt(1, tribune.getNumber());
			stm.executeUpdate();
			int id= getMaxId();
			for(Sector sector : tribune.getSectors()){
				SectorLogic logic= SectorLogic.getInstance();
				int sectorId= logic.addSector(sector);
				if(sectorId != -1){
					stm= conn.prepareStatement(Statements.INSERT_TRIB_SECTOR);
					stm.setInt(1, id);
					stm.setInt(2, sectorId);
					stm.executeUpdate();
				}
			}
			return id;
		} catch (SQLException e) {
			throw e;
			
		}
	}
	/**
	 * removes tribune of <code>id</code> from database
	 * @param id id of tribune
	 * @throws SQLException
	 */
	public void removeTribune(int id) throws SQLException{
		Connection conn= ConnectionManager.getManager().getConnection();
		try {
			PreparedStatement stm= conn.prepareStatement(Statements.SELECT_TRIB_SECTOR);
			stm.setInt(1, id);
			ResultSet rs= stm.executeQuery();
			SectorLogic logic= SectorLogic.getInstance();
			while(rs.next()){
				logic.removeSector(rs.getInt("sectorID"));
			}
			stm= conn.prepareStatement(Statements.DELETE_TRIBUNE);
			stm.setInt(1, id);
			stm.executeUpdate();
			
		} catch (SQLException e) {
			throw e;
		}
	}
	/**
	 * gets tribune by <code>id</code> from database and returns it as Tribune
	 * object
	 * @param id id of tribune
	 * @return
	 */
	public Tribune getTribuneById(int id){
		Tribune tribune= null;
		int tribuneNum= -1;
		Connection conn= ConnectionManager.getManager().getConnection();
		try {
			tribuneNum= getTribuneNumberById(id);
			tribune= new Tribune();
			
			PreparedStatement stm= conn.prepareStatement(Statements.SELECT_TRIB_SECTOR);
			stm.setInt(1, id);
			ResultSet result= stm.executeQuery();
			SectorLogic logic= SectorLogic.getInstance();
			while(result.next()){
				int sectorId= result.getInt("sectorID");
				tribune.addSector(logic.getSectorByID(sectorId));
			}
				
			
			int capacity=0;
			for(Sector sec : tribune.getSectors()){
				capacity+=sec.getCapacity();
			}
			tribune.setCapacity(capacity);
			tribune.setNumber(tribuneNum);
			
	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return tribune;
		
	}
	/**
	 * gets tribune number by tribune id
	 * @param id number of tribune
	 * @return
	 */
	public int getTribuneNumberById(int id){
		Connection conn= ConnectionManager.getManager().getConnection();
		int tribuneNum= -1;
		try {
			PreparedStatement stm= conn.prepareStatement(Statements.SELECT_TRIBUNE);
			stm.setInt(1, id);
			ResultSet rs= stm.executeQuery();

			while(rs.next()){
				tribuneNum= rs.getInt("number");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return tribuneNum;
	}
	/**
	 * 
	 * @return instance of TribuneLogic
	 */
	public static TribuneLogic getInstance(){
		if(tribuneLogic == null)
			tribuneLogic= new TribuneLogic();
		return tribuneLogic;
	}
	
	private int getMaxId() throws SQLException{
		Connection conn= ConnectionManager.getManager().getConnection();
		try {
			int maxId;
			Statement st= conn.createStatement();
			ResultSet rs= st.executeQuery(Statements.MAX_TRIBUNE_ID);

			while(rs.next()){
				maxId= rs.getInt("maxID");
				return maxId;
			}		
		} catch (SQLException e) {
			throw e;
		}
		
		
		return -1;
	}
}
